import hashlib
import itertools
import logging
import json
import time
from functools import reduce
from operator import or_

from flask import current_app as app, url_for, request_started
from flask_login import current_user, AnonymousUserMixin, UserMixin
from passlib.apps import custom_app_context as pwd_context
from sqlalchemy.exc import DBAPIError
from sqlalchemy.dialects import postgresql

from sqlalchemy_utils import EmailType
from sqlalchemy_utils.models import generic_repr

from redash import redis_connection
from redash.utils import generate_token, utcnow

from .base import db, Column, GFKBase, key_type, primary_key
from .mixins import TimestampMixin, BelongsToOrgMixin
from .types import json_cast_property, MutableDict, MutableList

logger = logging.getLogger(__name__)

################人员信息20230225###############################################
@generic_repr("id", "code", "name")
class Employee(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="employee")
    bill_date = Column(db.Date) #日期
    code = Column(db.String(100)) #职员编码
    name = Column(db.String(255)) #职员名称
    sex =  Column(db.String(15), nullable=True) #--性别
    mobile = Column(db.String(15), nullable=True) #手机号
    contacts = Column(db.String(255), nullable=True) #联系人
    contactsmobile = Column(db.String(15), nullable=True) #联系方式
    wechat = Column(db.String(255), nullable=True) #微信
    idcard = Column(db.String(20), nullable=True) #身份证号
    major = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    )  #毕业专业
    schooled = Column(db.String(255), nullable=True) #学历
    certificate = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    )  #证书
    location = Column(db.String(255), nullable=True) #地区
    socialsecurity =  Column(db.Integer, default=0, nullable=True) #--社保情况（无，有）
    area = Column(db.String(255), nullable=True) #社保地区
    threeperson = Column(db.String(255), nullable=True) #三类人员
    biddingexit = Column(db.String(255), nullable=True) #招标出场（是否)
    certificatestatus = Column(db.String(255), nullable=True) #证书状态 不限，初始注册，转注册
    price = Column(db.Numeric,default=0,  nullable=True) #预计金额
    frontmoney = Column(db.Numeric,default=0,  nullable=True)#定金
    periodunit = Column(db.String(255), nullable=True) #期限单位
    periods =  Column(db.Integer, default=0, nullable=True) #--期限
    informationfee = Column(db.Numeric,default=0,  nullable=True)#信息费
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    contracttype = Column(db.String(255), nullable=True) #签约类型
    bytstatus = Column(db.String(255), nullable=True) #洽谈中、签约、完成 、到期、闲置
    signingtime = Column(db.Date, nullable=True) #签约时间
    expirationtime = Column(db.Date, nullable=True) #到期时间    
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    openseapool =  Column(db.Integer, default=0, nullable=True) #---是否公海池 0否；1是
    openshow =  Column(db.Integer, default=0, nullable=True) #---是否公开 0否；1是
    maintainer = Column(db.Integer, default=0, nullable=True) #维护人
    maintainermove = Column(db.Integer, default=0, nullable=True) #维护人转移
    signatory = Column(db.Integer, default=0, nullable=True) #签约人
    userid = Column(db.Integer) #录入人ID
    orgunitid = Column(db.Integer, default=0, nullable=True) #---部门ID
    signamount = Column(db.Numeric,default=0,  nullable=True) #金额
    excelin	=  Column(db.Integer, default=0, nullable=True) #是否由EXCEL导入
    contractcode = Column(db.String(255), nullable=True) #合同编码
    accountname = Column(db.String(255), nullable=True) #户名
    accountcode = Column(db.String(255), nullable=True) #账户
    accountbank = Column(db.String(255), nullable=True) #开户行

    __tablename__ = "employee"
    __table_args__ = (db.Index("employee_org_code", "org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "bill_date": self.bill_date,
            "code": self.code,
            "name": self.name,
            "sex": self.sex,
            "mobile": self.mobile,
            "contacts": self.contacts,
            "contactsmobile": self.contactsmobile,
            "wechat": self.wechat,
            "idcard": self.idcard,
            "major": self.major,
            "schooled": self.schooled,
            "certificate": self.certificate,
            "location": self.location,
            "socialsecurity": self.socialsecurity,
            "area": self.area,
            "threeperson": self.threeperson,
            "biddingexit": self.biddingexit,
            "certificatestatus": self.certificatestatus,
            "price": self.price,
            "frontmoney": self.frontmoney,
            "periodunit": self.periodunit,
            "periods": self.periods,
            "informationfee": self.informationfee,
            "message": self.message,
            "contracttype": self.contracttype,
            "bytstatus": self.bytstatus,
            "signingtime": self.signingtime,
            "expirationtime": self.expirationtime,
            "status": self.status,
            "openseapool": self.openseapool,
            "openshow": self.openshow,
            "maintainer": self.maintainer,
            "maintainermove": self.maintainermove,
            "signatory": self.signatory,
            "userid": self.userid,
            "orgunitid": self.orgunitid,
            "signamount": self.signamount,
            "excelin": self.excelin,
            "contractcode": self.contractcode,
            "accountname": self.accountname,
            "accountcode": self.accountcode,
            "accountbank": self.accountbank,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()
    
    @classmethod
    def check_employee_field(cls,employeeid,field,values):
        strsql="""select case when openseapool=0 then '私有池：'|| t2.name else '公有池' end as message 
            From public.employee t1 left join public.users t2 on t1.maintainer=t2.id
            where t1."""+field+"""='"""+values+"""' and t1.id<>"""+str(employeeid)
        return db.session.execute(strsql).fetchone()
    
    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('employee_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()
################变更列表20200613###############################################
@generic_repr("id")
class EmployeeChange(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="employeechange")
    employeeid = Column(db.Integer) #人员表ID
    bill_date = Column(db.Date) #日期
    code = Column(db.String(100)) #职员编码
    name = Column(db.String(255)) #职员名称
    major = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    )  #毕业专业
    area = Column(db.String(500), nullable=True) #地区
    mobile = Column(db.String(15), nullable=True) #地区
    userid = Column(db.Integer) #操作员ID
    bytstatus = Column(db.String(255), nullable=True) #洽谈中、签约、完成 、到期、闲置
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    openseapool =  Column(db.Integer, default=0, nullable=True) #---是否公海池 0否；1是
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项

    __tablename__ = "employeechange"

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "bill_date": self.bill_date,
            "employeeid":self.employeeid,
            "code": self.code,
            "name": self.name,
            "major": self.major,
            "area": self.area,
            "mobile": self.mobile,
            "userid": self.userid,
            "bytstatus": self.bytstatus,
            "status": self.status,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()
    
    @classmethod
    def get_by_employeeid(cls,employeeid):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select t1.*,
            to_char(t1.updated_at, 'YYYY-MM-DD') as updated_atformat
            from public.employeechange t1
         where t1.status=2 and t1.empolyeeid="""+str(employeeid)
        strsql=strsql+"""  order by t1.updated_at desc) t """
        return db.session.execute(strsql).fetchone()
    
    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('employeechange_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()

################附件###############################################
@generic_repr("id")
class Attaches(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="attaches")
    userid = Column(db.Integer) #--操作员ID
    type = Column(db.String(255)) #类型
    annex = Column(
        MutableList.as_mutable(postgresql.JSON),
        nullable=True,
        server_default="{}",
        default={},
    ) #附件
    status = Column(db.Integer, default=0, nullable=True) #--状态
    sourcetype = Column(db.String(255))  # 来源单据类型
    sourceid = Column(db.Integer, default=0) #--来源单据ID

    __tablename__ = "attaches"
    __table_args__ = (db.Index("attaches_org_sourcetype_sourceid","org_id","type","sourcetype","sourceid",  unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.id,
            self.sourceid,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "type": self.type,
            "annex": self.annex,
            "status": self.status,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "sourcetype": self.sourcetype,
            "sourceid": self.sourceid,
        }
    
    @classmethod
    def get_by_id(cls, sourceid,type):
        return cls.query.filter(cls.sourceid == sourceid, cls.type == type).one()

################职员跟进表###############################################
@generic_repr("id")
class EmployeeActivity(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    userid = Column(db.Integer) #--操作员ID
    type = Column(db.Integer, default=0) #--类别0 跟进内容，1审批过程
    employeeid = Column(db.Integer) #--职员ID
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    sourcetype = Column(db.String(255))  # 来源单据类型

    __tablename__ = "employeeactivity"
    # __table_args__ = (db.Index("id",  unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.id,
            self.employeeid,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "type": self.type,
            "employeeid": self.employeeid,
            "message": self.message,
            "sourcetype": self.sourcetype,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

    @classmethod
    def get_by_employeeid(cls,employeeid,sourcetype):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select t1.id ,t1.userid , t1.employeeid, t1.message  as message,
            to_char(t1.updated_at, 'YYYY-MM-DD') as updated_at,
            t1.created_at as created_at ,
            to_char(t1.created_at, 'YYYY-MM-DD') as created_ed,
            t1.sourcetype,t3.name as orgunitname,t4.name as username
            from public.employeeactivity t1 
            left join public.users t4 on t1.userid=t4.id left join public.orgunit t3 on t4.orgunit_id=t3.id
         where t1.employeeid="""+str(employeeid) +""" and t1.sourcetype='"""+str(sourcetype)+"""'"""
        strsql=strsql+"""  order by t1.updated_at desc) t """
        return db.session.execute(strsql).fetchone()

    
################款项表20230315###############################################
@generic_repr("id", "code", "name")
class Payment(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="payment")
    bill_date = Column(db.Date) #日期
    type = Column(db.Integer, default=0) #类别0 付款，1收款
    customerid = Column(db.Integer, default=0, nullable=True) #--客户ID
    employeeid = Column(db.Integer, default=0, nullable=True) #--人员ID
    code = Column(db.String(100)) #编码
    name = Column(db.String(255)) #名称
    amount = Column(db.Numeric,default=0,  nullable=True) #金额
    othertype = Column(db.String(255), nullable=True) #其他费用
    otheramount = Column(db.Numeric,default=0,  nullable=True)#其他费用
    payname = Column(db.String(255), nullable=True) #-收款人
    accountname = Column(db.String(255), nullable=True) #-户名
    accountcode = Column(db.String(255), nullable=True) #-账户
    accountbank = Column(db.String(255), nullable=True) #-开户行
    paydate = Column(db.Date) #日期
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    signingtime = Column(db.Date, nullable=True) #签约时间
    expirationtime = Column(db.Date, nullable=True) #到期时间    
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    orgunitid = Column(db.Integer, default=0, nullable=True) #---部门ID
    userid = Column(db.Integer) #录入人ID

    __tablename__ = "payment"
    __table_args__ = (db.Index("payment_org_type_code_signingtime", "org_id","type","signingtime", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "bill_date": self.bill_date,
            "type": self.type,
            "customerid": self.customerid,
            "employeeid":self.employeeid,
            "code": self.code,
            "name": self.name,
            "amount": self.amount,
            "othertype": self.othertype,
            "otheramount": self.otheramount,
            "payname": self.payname,
            "accountname": self.accountname,
            "accountcode": self.accountcode,
            "accountbank": self.accountbank,
            "paydate": self.paydate,
            "message": self.message,
            "signingtime": self.signingtime,
            "expirationtime": self.expirationtime,
            "status": self.status,
            "userid": self.userid,
            "orgunitid": self.orgunitid,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()
    
    @classmethod
    def get_repeat_field(cls,tables,paymentid,field,values):
        strsql="""select count(*) as nums From """+tables+"""
         where """+field+"""='"""+values+"""' and id<>"""+str(paymentid)
        return db.session.execute(strsql).fetchone()
    

    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('payment_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()
    
    ################款项表明细###############################################
@generic_repr("id")
class PaymentActivity(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    paymentid = Column(db.Integer) #--ID
    projectid = Column(db.Integer) #--项目ID
    projectdetailid = Column(db.Integer) #--项目明细ID
    employeeid = Column(db.String(255), nullable=True) #--人员ID
    code = Column(db.String(255), nullable=True) #--编码
    name = Column(db.String(255), nullable=True) #--名称
    contractcode = Column(db.String(255), nullable=True) #--合同编码
    amount = Column(db.Numeric,default=0)#金额
    othertype = Column(db.String(255), nullable=True) #--其他费用
    otheramount = Column(db.Numeric,default=0)#其他费用
    signingtime = Column(db.Date, nullable=True) #签约时间
    expirationtime = Column(db.Date, nullable=True) #到期时间 
    maintainer = Column(db.Integer, default=0, nullable=True) #维护人
    signatory = Column(db.Integer, default=0, nullable=True) #签约人
    recipient = Column(db.Integer, default=0, nullable=True) #收证人ID 
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项

    __tablename__ = "paymentactivity"

    def __str__(self):
        return "%s,%s" % (
            self.id,
            self.paymentid,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "userid": self.userid,
            "paymentid": self.paymentid,
            "projectid": self.projectid,
            "projectdetailid": self.projectdetailid,
            "employeeid": self.employeeid,
            "code": self.code,
            "name": self.name,
            "contractcode": self.contractcode,
            "amount": self.amount,
            "othertype": self.othertype,
            "otheramount": self.otheramount,
            "signingtime": self.signingtime,
            "expirationtime": self.expirationtime,
            "maintainer": self.maintainer,
            "signatory": self.signatory,
            "recipient": self.recipient,
            "message": self.message,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }
    
    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()

################项目信息20230324###############################################
@generic_repr("id", "code", "name")
class Project(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    org_id = Column(key_type("Organization"), db.ForeignKey("organizations.id"))
    org = db.relationship("Organization", back_populates="project")
    bill_date = Column(db.Date) #日期
    customerid = Column(db.Integer) #客户
    code = Column(db.String(100)) #编码
    name = Column(db.String(255)) #名称
    neednum =  Column(db.Integer, default=0, nullable=True) #--需求数量
    amount = Column(db.Numeric,default=0,  nullable=True) #挂靠费用

    major = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    )  #需求专业
    schooled = Column(db.String(255), nullable=True) #学历要求
    certificate = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    )  #需求证书
    location = Column(db.String(255), nullable=True) #挂靠地点
    socialsecurity =  Column(db.Integer, default=0, nullable=True) #--社保情况（无，有）
    area = Column(db.String(255), nullable=True) #社保地区
    bytstatus = Column(db.String(255), nullable=True) #敲定、 已签、带证报价
    periodunit = Column(db.String(255), nullable=True) #期限单位
    periods =  Column(db.Integer, default=0, nullable=True) #--期限
    signingtime = Column(db.Date, nullable=True) #签约时间
    expirationtime = Column(db.Date, nullable=True) #到期时间    
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    purpose = Column(db.String(255), nullable=True) #证书用途
    certificatestatus = Column(db.String(255), nullable=True) #证书状态 不限，初始注册，转注册
    locationneed = Column(db.String(255), nullable=True) #证书地区要求 不限，附近省份，省内
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    signatory = Column(db.Integer, default=0, nullable=True) #签约人
    userid = Column(db.Integer) #录入人ID
    orgunitid = Column(db.Integer, default=0, nullable=True) #---部门ID
    contractcode = Column(db.String(255), nullable=True) #合同编码
    threeperson = Column(db.String(255), nullable=True) #三类人员

    __tablename__ = "project"
    __table_args__ = (db.Index("project_org_code", "org_id", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "bill_date": self.bill_date,
            "customerid": self.customerid,
            "code": self.code,
            "name": self.name,
            "neednum": self.neednum,
            "amount": self.amount,
            "major": self.major,
            "schooled": self.schooled,
            "certificate": self.certificate,
            "location": self.location,
            "socialsecurity": self.socialsecurity,
            "area": self.area,
            "bytstatus": self.bytstatus,
            "periodunit": self.periodunit,
            "periods": self.periods,
            "signingtime": self.signingtime,
            "expirationtime": self.expirationtime,
            "status": self.status,
            "purpose": self.purpose,
            "certificatestatus": self.certificatestatus,
            "locationneed": self.locationneed,
            "message": self.message,
            "signatory": self.signatory,
            "userid": self.userid,
            "orgunitid": self.orgunitid,
            "contractcode": self.contractcode,
            "threeperson": self.threeperson,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()
    
    @classmethod
    def get_repeat_field(cls,tables,projectid,field,values):
        strsql="""select count(*) as nums From """+tables+"""
         where """+field+"""='"""+values+"""' and id<>"""+str(projectid)
        return db.session.execute(strsql).fetchone()
    
    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('project_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()
    
    ################项目明细信息20230324###############################################
@generic_repr("id", "code", "name")
class ProjectDetail(TimestampMixin, BelongsToOrgMixin, db.Model):
    id = Column(db.Integer, primary_key=True)
    bill_date = Column(db.Date,  nullable=True) #日期
    projectid = Column(db.Integer) #项目ID
    employeeid = Column(db.Integer) #人员ID
    code = Column(db.String(100)) #编码
    name = Column(db.String(255)) #名称
    amount = Column(db.Numeric,default=0,  nullable=True) #挂靠费用
    signamount = Column(db.Numeric,default=0,  nullable=True) #金额
    major = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    )  #需求专业
    schooled = Column(db.String(255), nullable=True) #学历要求
    certificate = Column(
        MutableList.as_mutable(postgresql.ARRAY(db.String(255))), nullable=True
    )  #需求证书
    location = Column(db.String(255), nullable=True) #地区
    socialsecurity =  Column(db.Integer, default=0, nullable=True) #--社保情况（无，有）
    area = Column(db.String(255), nullable=True) #社保地区
    periodunit = Column(db.String(255), nullable=True) #期限单位
    periods =  Column(db.Integer, default=0, nullable=True) #--期限
    signingtime = Column(db.Date, nullable=True) #签约时间
    expirationtime = Column(db.Date, nullable=True) #到期时间    
    message = Column(db.Text, default=None, nullable=True)  #  纪录事项
    contractcode = Column(db.String(255), nullable=True) #合同编码
    userid = Column(db.Integer) #录入人ID
    status =  Column(db.Integer, default=0, nullable=True) #--状态
    orgunitid = Column(db.Integer, default=0, nullable=True) #---部门ID

    __tablename__ = "projectdetail"
    __table_args__ = (db.Index("projectdetail_projectid_code", "projectid", "code", unique=True),)

    def __str__(self):
        return "%s,%s" % (
            self.code,
            self.name,
        )

    def to_dict(self):
        return {
            "id": self.id,
            "projectid": self.projectid,
            "employeeid": self.employeeid,
            "code": self.code,
            "name": self.name,
            "amount": self.amount,
            "signamount": self.signamount,
            "major": self.major,
            "schooled": self.schooled,
            "certificate": self.certificate,
            "location": self.location,
            "socialsecurity": self.socialsecurity,
            "area": self.area,
            "periodunit": self.periodunit,
            "periods": self.periods,
            "signingtime": self.signingtime,
            "expirationtime": self.expirationtime,
            "message": self.message,
            "userid": self.userid,
            "contractcode": self.contractcode,
            "updated_at": self.updated_at,
            "created_at": self.created_at,
            "bill_date": self.bill_date,
            "status": self.status,
            "orgunitid": self.orgunitid,
        }

    @classmethod
    def get_by_id(cls, id):
        return cls.query.filter(cls.id == id).one()
    
    @classmethod
    def get_repeat_employee(cls,projectid):
        strsql="""select COALESCE(max(t1.neednum-COALESCE(t2.nums,0)),0) as nums
            From public.project t1 left join(select count(*) as nums,projectid 
            from public.projectdetail where projectid="""+str(projectid)+""" group by projectid) t2 on t1.id=t2.projectid
            where t1.id="""+str(projectid)
        return db.session.execute(strsql).fetchone()
    
    @classmethod
    def get_id(cls):
        strsql="""select array_to_json(array_agg(row_to_json(t))) from (select nextval('projectdetail_id_seq'::regclass) as id) t """
        return db.session.execute(strsql).fetchone()